package sk.vasicek.drinkmanager.service;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import sk.vasicek.drinkmanager.domain.DrinkPoint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DrinkPlaceHelper extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 3;
	private static final String DATABASE_NAME = "drink_database";
	public static final String ID = "map_id";
	public static final String PLACE = "map_place";
	private static final String NOTE = "map_note";
	public static final String LATITUDE = "map_latitude";
	public static final String LONGITUDE = "map_longitude";
	public static final String TIME = "map_time";
	public static final String RATING = "map_rating";
	private static final String DICTIONARY_TABLE_NAME = "dictionary";
	private static final String DRINK_TABLE_NAME = "drink";
	private static final String DRINKS_TABLE_CREATE = "CREATE TABLE " + DRINK_TABLE_NAME + " (" + ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + PLACE + " TEXT, " + NOTE + " TEXT, " + RATING
			+ " DOUBLE, " + LATITUDE + " DOUBLE, " + LONGITUDE + " DOUBLE, " + TIME + " TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);";
	private static final String DICTIONARY_TABLE_DROP = "DROP TABLE " + DICTIONARY_TABLE_NAME + ";";
	private static final String DRINKS_TABLE_DROP = "DROP TABLE " + DRINK_TABLE_NAME + ";";

	public DrinkPlaceHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DRINKS_TABLE_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Version 0.6 -> 0.7
		if (oldVersion == 1 && newVersion == 2) {
			db.execSQL(DRINKS_TABLE_CREATE);
			ArrayList<DrinkPoint> drinkPoints = new ArrayList<DrinkPoint>();
			Cursor cursor = db.query(DICTIONARY_TABLE_NAME, new String[] { TIME, PLACE, LATITUDE, LONGITUDE }, null, null, null, null, TIME + " DESC");
			while (cursor.getCount() > 0 && cursor.moveToNext()) {
				String place = cursor.getString(cursor.getColumnIndex(PLACE));
				Timestamp timeOfLocation = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME)));
				double latitude = cursor.getDouble(cursor.getColumnIndex(LATITUDE));
				double longitude = cursor.getDouble(cursor.getColumnIndex(LONGITUDE));
				DrinkPoint drinkPoint = new DrinkPoint(latitude, longitude, place, "Location: " + String.valueOf(latitude).subSequence(0, 6) + " " + String.valueOf(longitude).subSequence(0, 6) + " "
						+ timeOfLocation.getDate() + "." + timeOfLocation.getMonth() + ". " + timeOfLocation.getHours() + ":" + timeOfLocation.getMinutes() + ":" + timeOfLocation.getSeconds());
				drinkPoints.add(drinkPoint);
			}
			for (DrinkPoint drinkPoint : drinkPoints) {
				ContentValues contentValues = new ContentValues();
				contentValues.put(LATITUDE, drinkPoint.getLatitude());
				contentValues.put(LONGITUDE, drinkPoint.getLongitude());
				contentValues.put(PLACE, drinkPoint.getTitle());
				db.insert(DRINK_TABLE_NAME, null, contentValues);
			}
			db.execSQL(DICTIONARY_TABLE_DROP);
		}
		if (oldVersion == 2 && newVersion == 3) {
			// Version 0.7.5.5 -> 0.7.6
			ArrayList<DrinkPoint> drinkPoints = new ArrayList<DrinkPoint>();
			Cursor cursor = db.query(DRINK_TABLE_NAME, new String[] { TIME, PLACE, LATITUDE, LONGITUDE }, null, null, null, null, TIME + " DESC");
			while (cursor.getCount() > 0 && cursor.moveToNext()) {
				String place = cursor.getString(cursor.getColumnIndex(PLACE));
				Timestamp timeOfLocation = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME)));
				double latitude = cursor.getDouble(cursor.getColumnIndex(LATITUDE));
				double longitude = cursor.getDouble(cursor.getColumnIndex(LONGITUDE));
				DrinkPoint drinkPoint = new DrinkPoint(latitude, longitude, place, "Location: " + String.valueOf(latitude).subSequence(0, 6) + " " + String.valueOf(longitude).subSequence(0, 6) + " "
						+ timeOfLocation.getDate() + "." + timeOfLocation.getMonth() + ". " + timeOfLocation.getHours() + ":" + timeOfLocation.getMinutes() + ":" + timeOfLocation.getSeconds());
				drinkPoints.add(drinkPoint);
			}

			db.execSQL(DRINKS_TABLE_DROP);
			db.execSQL(DRINKS_TABLE_CREATE);
			for (DrinkPoint drinkPoint : drinkPoints) {
				ContentValues contentValues = new ContentValues();
				contentValues.put(PLACE, drinkPoint.getTitle());
				contentValues.put(NOTE, drinkPoint.getSnippet());
				contentValues.put(RATING, 0);
				contentValues.put(LATITUDE, drinkPoint.getLatitude());
				contentValues.put(LONGITUDE, drinkPoint.getLongitude());

				db.insert(DRINK_TABLE_NAME, null, contentValues);
			}
		}
		if (oldVersion == 1 && newVersion == 3) {
			ArrayList<DrinkPoint> drinkPoints = new ArrayList<DrinkPoint>();
			Cursor cursor = db.query(DICTIONARY_TABLE_NAME, new String[] { TIME, PLACE, LATITUDE, LONGITUDE }, null, null, null, null, TIME + " DESC");
			while (cursor.getCount() > 0 && cursor.moveToNext()) {
				String place = cursor.getString(cursor.getColumnIndex(PLACE));
				Timestamp timeOfLocation = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME)));
				double latitude = cursor.getDouble(cursor.getColumnIndex(LATITUDE));
				double longitude = cursor.getDouble(cursor.getColumnIndex(LONGITUDE));
				DrinkPoint drinkPoint = new DrinkPoint(latitude, longitude, place, "Location: " + String.valueOf(latitude).subSequence(0, 6) + " " + String.valueOf(longitude).subSequence(0, 6) + " "
						+ timeOfLocation.getDate() + "." + timeOfLocation.getMonth() + ". " + timeOfLocation.getHours() + ":" + timeOfLocation.getMinutes() + ":" + timeOfLocation.getSeconds());
				drinkPoints.add(drinkPoint);
			}

			db.execSQL(DICTIONARY_TABLE_DROP);
			db.execSQL(DRINKS_TABLE_CREATE);

			for (DrinkPoint drinkPoint : drinkPoints) {
				ContentValues contentValues = new ContentValues();
				contentValues.put(PLACE, drinkPoint.getTitle());
				contentValues.put(NOTE, drinkPoint.getSnippet());
				contentValues.put(RATING, 0);
				contentValues.put(LATITUDE, drinkPoint.getLatitude());
				contentValues.put(LONGITUDE, drinkPoint.getLongitude());

				db.insert(DRINK_TABLE_NAME, null, contentValues);
			}
		}
	}

	public ArrayList<DrinkPoint> getAllDrinkPoints() {
		SQLiteDatabase db = getReadableDatabase();
		ArrayList<DrinkPoint> drinkPoints = new ArrayList<DrinkPoint>();
		Cursor cursor = db.query(DRINK_TABLE_NAME, new String[] { ID, TIME, PLACE, NOTE, RATING, LATITUDE, LONGITUDE }, null, null, null, null, TIME + " DESC");
		while (cursor.getCount() > 0 && cursor.moveToNext()) {

			Integer id = cursor.getInt(cursor.getColumnIndex(ID));
			String place = cursor.getString(cursor.getColumnIndex(PLACE));
			String note = cursor.getString(cursor.getColumnIndex(NOTE));
			double rating = cursor.getDouble(cursor.getColumnIndex(RATING));
			Timestamp timeOfLocation = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME)));
			Date time = new Date(timeOfLocation.getTime());
			double latitude = cursor.getDouble(cursor.getColumnIndex(LATITUDE));
			double longitude = cursor.getDouble(cursor.getColumnIndex(LONGITUDE));
			DrinkPoint drinkPoint = new DrinkPoint(id, latitude, longitude, place, note, rating, time);
			drinkPoints.add(drinkPoint);
		}
		db.close();
		return drinkPoints;
	}

	public void insertDrinkMapPoint(String name, String note, double rating, double latitude, double longitude) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put(LATITUDE, latitude);
		contentValues.put(LONGITUDE, longitude);
		contentValues.put(PLACE, name);
		contentValues.put(NOTE, note);
		contentValues.put(RATING, rating);
		db.insert(DRINK_TABLE_NAME, null, contentValues);
		db.close();
	}

	public void updateDrinkMapPoint(Integer id, String name, String note, double rating) {
		if (id != null && !"".equals(id.toString())) {
			SQLiteDatabase db = getWritableDatabase();
			ContentValues contentValues = new ContentValues();
			contentValues.put(PLACE, name);
			contentValues.put(NOTE, note);
			contentValues.put(RATING, rating);
			db.update(DRINK_TABLE_NAME, contentValues, ID + " = " + id.toString(), null);
			db.close();
		}
	}

	public void removeDrinkMapPoint(Integer id) {
		if (id != null && !"".equals(id.toString())) {
			SQLiteDatabase db = getWritableDatabase();

			db.delete(DRINK_TABLE_NAME, ID + " = " + id.toString(), null);
			db.close();
		}
	}

	public void removeDrinkMapPoint(DrinkPoint drinkPoint) {
		if (drinkPoint != null && drinkPoint.getId() != null && !"".equals(drinkPoint.getId())) {
			SQLiteDatabase db = getWritableDatabase();

			db.delete(DRINK_TABLE_NAME, ID + " = " + drinkPoint.getId().toString(), null);
			db.close();
		}
	}

	public void updateDrinkMapPoint(Integer id, DrinkPoint drinkPoint) {
		if (id != null && !"".equals(id.toString())) {
			SQLiteDatabase db = getWritableDatabase();
			ContentValues contentValues = new ContentValues();
			contentValues.put(PLACE, drinkPoint.getTitle());
			contentValues.put(NOTE, drinkPoint.getSnippet());
			contentValues.put(RATING, drinkPoint.getRating());
			db.update(DRINK_TABLE_NAME, contentValues, ID + " = " + id.toString(), null);
			db.close();
		}
	}

	public DrinkPoint getDrinkMapPointByID(Integer id) {
		if (id != null && !"".equals(id.toString())) {
			SQLiteDatabase db = getReadableDatabase();
			Cursor cursor = db.query(DRINK_TABLE_NAME, new String[] { TIME, PLACE, NOTE, RATING, LATITUDE, LONGITUDE }, ID + " = " + id.toString(), null, null, null, TIME + " DESC");
			DrinkPoint drinkPoint = null;
			if (cursor.getCount() > 0 && cursor.getCount() == 1 && cursor.moveToNext()) {
				String place = cursor.getString(cursor.getColumnIndex(PLACE));
				String note = cursor.getString(cursor.getColumnIndex(NOTE));
				double rating = cursor.getDouble(cursor.getColumnIndex(RATING));
				Timestamp timeOfLocation = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME)));
				Date time = new Date(timeOfLocation.getTime());
				double latitude = cursor.getDouble(cursor.getColumnIndex(LATITUDE));
				double longitude = cursor.getDouble(cursor.getColumnIndex(LONGITUDE));
				drinkPoint = new DrinkPoint(id, latitude, longitude, place, note, rating, time);
			}
			db.close();
			return drinkPoint;
		}
		return null;
	}
}
